indexer-db reads are not network-isolated — every query omits the `network` predicate the Supabase path applies

[dcccrypto]

Summary

Every read in app/lib/indexer-db.ts queries the shared indexer Postgres without a network predicate, while the Supabase paths in the same routes filter on network. The two backends therefore disagree about network isolation: whichever one a deployment happens to use decides whether a devnet trade can appear in mainnet figures.

Raised out of review on #2512 (CodeRabbit flagged the missing predicate on the new queryTraderStatsAggregate). It is not specific to that function — I checked the rest of the module and none of them filter either, so fixing one in isolation would make trader stats network-isolated while candles, funding and trade history on the same database stayed cross-network.

Affected

app/lib/indexer-db.ts — no occurrence of network in any WHERE clause. The column exists; the module's own schema comment documents it:

 *   trades            — id, slab_address, trader, tx_signature, side, size,
 *                       price, fee, created_at (timestamptz), network, ...
 *   funding_history   — ... timestamp (timestamptz), network

Contrast the Supabase fallback in app/app/api/trader/[wallet]/stats/route.ts, which does:

.eq("network", getServerNetwork())

Impact

Bounded by deployment: it only matters where one Postgres instance holds rows for more than one network. If INDEXER_DATABASE_URL always points at a single-network database, this is latent rather than live — which is worth establishing before changing anything, because it decides whether this is a bug or a hardening.

Where it does bite, the symptom is quiet: devnet activity inflating mainnet trade counts, volume and "unique markets", with no error and no visible marker.

Prerequisite before fixing — please check this first

The two sides derive the network from different environment variables:

side	source	values
indexer writes	getNetwork() (@percolatorct/shared, dist/db/queries.js) → process.env.NETWORK	"mainnet" | "devnet"
launch reads	getServerNetwork() (app/lib/supabase.ts) → process.env.NEXT_PUBLIC_DEFAULT_NETWORK	"mainnet" | "devnet"

The vocabularies match, so a filter would compare like with like. But both default to devnet when their variable is unset, so a deployment running NETWORK=mainnet on the indexer with NEXT_PUBLIC_DEFAULT_NETWORK unset on launch would go from cross-network reads to zero rows for every wallet the moment a predicate is added.

Silently zeroing an endpoint is a worse failure than the one being fixed, so confirming both variables are set consistently in each environment should come before the code change.

Suggested fix

Once the env question is settled: thread getServerNetwork() through indexer-db.ts and add AND network = ${network} to each query, so both backends agree. Worth doing in one pass rather than per-function, so the module cannot end up half-isolated.

Not doing this in #2512

That PR is scoped to the 10k truncation in /api/trader/:wallet/stats, and its aggregate faithfully preserves the pre-existing (unfiltered) behaviour of the query it replaces. Fixing network isolation there alone would leave the module inconsistent and would carry the deployment risk above without anyone having checked it.

[dcccrypto]

Confirmed still live — keeping open. It has a dependency worth naming.

Every query in app/lib/indexer-db.ts filters on slab_address, trader or created_at, and none filter on network:

:143 WHERE slab_address = ${slabAddress}
:198 WHERE slab_address = ${slabAddress}
:464 WHERE trader = ${wallet}
:498 WHERE trader = ${wallet} AND slab_address = ${slabFilter}
:524 WHERE trader = ${wallet} AND slab_address = ${slabFilter}

network appears exactly twice in the file, both in the schema doc comment (:15, :19) — so the column exists on trades and funding_history, it is simply never used as a predicate.

Why I have not just added AND network = ${network}: there is no server-side network source to pass in. The only resolver is app/lib/config.ts:7 getNetwork(), which reads localStorage (:17) — and this file is explicitly "Loaded only in Node.js (Next.js server-side route handlers, never the browser)". So the value is not reachable here.

That is the same root as #2229 (getNetwork() reads from localStorage without a deployment-level source). Fixing this one properly means first deciding where the server learns its network from — an env var, request context, or a build-time constant — which is a deployment-topology call.

Recommend sequencing #2229 first; this becomes mechanical afterwards.

[dcccrypto]

Correction to my previous comment — I was wrong that this is blocked on #2229.

I said there was no server-side network source. There is: getServerNetwork() in app/lib/supabase.ts:19.

export function getServerNetwork(): DbNetwork {
  const net = process.env.NEXT_PUBLIC_DEFAULT_NETWORK?.trim();
  if (net === "mainnet") return "mainnet";
  return "devnet"; // fail-safe to devnet
}

It reads an env var rather than localStorage, so it works server-side, it fail-safes to devnet, and it is already used in 12 files — including app/app/api/stake/pools/route.ts:310, which does exactly the .eq("network", getServerNetwork()) scoping this issue asks for.

I reached the wrong conclusion by checking only config.ts's getNetwork() (the localStorage one) and stopping there. The dependency on #2229 I described does not exist.

So this is mechanical after all, and I am implementing it: 12 query sites in app/lib/indexer-db.ts read FROM trades / funding_history with no network predicate —

:142 :197 :326 :346 :393 :406 :419 :463 :497 :501 :523 :532

— and the column exists on both tables. Fix in progress.

[dcccrypto]

FIXED and merged in PR #2527. Closing — and the guard it shipped with has already earned its keep.

Every indexer-db read is now scoped to getServerNetwork(). Verified on merged playground (91dfff1a); the dedicated guard passes 3/3.

Two things worth recording

A quarter of the sites needed hand-patching. The automated pass caught 10 of 12; two COUNT queries used a single-line form the pattern missed. A sweep that reports "done" after a regex pass would have left those.

The guard caught a 13th site the same day it merged. #2512 (GH#2510) replaced the trader-stats row cap with a database aggregation — a strictly better fix — and the new query landed without a network predicate. The test named it exactly:

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

so a wallet's stats were summing devnet and mainnet trades into one answer: totalTrades, totalVolume, totalFees, uniqueMarkets and both timestamps. The sibling row-fetch query 40 lines below already had the predicate, so the two disagreed within one file.

That is the argument for the guard over the sweep. A cross-network sum is invisible on a single-network deployment and silently wrong on a dual one — no amount of careful reading reliably catches a missing WHERE clause, and the next one will be added by someone who never read this issue.

One correction to my own earlier note

I initially said this was blocked on #2229. It was not — getServerNetwork() already existed and was in use in 12 files. Corrected before writing the fix.

Launch suite: 3111 passed / 16 skipped / 0 failed.