Goal: SDK call for market volume over a time period

[vinarmani]

Warning

@vinarmani this Goal has no ETA. Add an estimated completion date before work begins.
See the Wizard docs for guidance.

@vinarmani

Framework only. The definitions and caveats are the parts worth holding. The
action signature and response shape are a starting point, not a decision.

Why this was created

The order book query surface covers the book, depth, and best prices, but not
activity. get_order_book, get_market_depth, and get_best_prices all answer
"what is resting right now". There is no action that answers "how much traded".

The data is already on the node. ob_order_events records every fill with
price, amount, outcome, participant, and block timestamp, so volume and unique
traders are computable without new state. What is missing is an action, and
therefore an SDK call.

Outcome

An SDK call returns volume and unique traders for a single order book over a
time period, in the same shape and at the same level as get_market_depth.

Current state

Nothing exists. The metrics are reachable today only by querying Postgres
directly, which requires node access and re-deriving the rule by hand each time.

Scope

Suggested action shape

Per bucket, matching get_market_depth. Callers that want a whole ladder
aggregate across its buckets, rather than the action taking a stream and a
settle time and baking the ladder structure into its signature.

CREATE OR REPLACE ACTION get_market_activity(
    $query_id INT,
    $from_ts  INT8,
    $to_ts    INT8
) PUBLIC VIEW RETURNS TABLE(
    bridge              TEXT,           -- units. volume is meaningless without it
    volume_cents        NUMERIC(78,0),  -- the headline figure
    direct_cents        NUMERIC(78,0),  -- counterparty trading
    mint_burn_cents     NUMERIC(78,0),  -- boundary arbitrage
    unique_traders      INT,
    fill_count          INT,            -- all fills, one row per economic event
    direct_fill_count   INT,            -- subset: counterparty matches only
    shares_traded       INT8,
    first_event_ts      INT8,           -- activity window actually observed
    last_event_ts       INT8,
    coverage_from_block INT8,           -- earliest retained event for this market
    coverage_complete   BOOL            -- false if the market predates retention
)

PUBLIC VIEW, so read only, with no consensus or fee implications.

Why one object rather than a volume scalar

Every field above falls out of the same single pass over the event rows, so
returning them together costs nothing beyond the scan that volume already
requires. Splitting them into separate actions would multiply the scans.

Three of them earn their place for reasons worth stating:

• bridge because volume is per collateral token and never summable across
  tokens. A bare number invites exactly the addition the definition forbids.
• direct_cents and mint_burn_cents because the split is diagnostic. Two
  markets with identical volume behave very differently if one is counterparty
  trading and the other is a single participant working the mint and burn
  boundary. They respond to different stimuli, and a consumer charting volume
  will want to know which it is looking at.
• coverage_from_block and coverage_complete because they turn the
  retention problem below from a silent wrong answer into a visible one.

A count is worth having next to unique_traders. A market with seventy small
matches and one with five large ones report the same volume and the same trader
count, and are not the same market.

Count fills on the same allowlist that defines volume. If a mint or a burn
counts as volume, it counts as a fill, for the same reason: it is an economic
decision with collateral moving. Excluding it from the count while including it
in the value would be inconsistent.

De-duplicate it the same way as well. Both patterns write two rows per
economic event, direct_buy_fill alongside direct_sell_fill for a match and
a YES row alongside a NO row for a mint or burn, so one row per event is the
count in both cases. direct_fill_count is then a subset of fill_count, and
the pair mirrors the direct_cents and mint_burn_cents split.

Definition to hold fixed

Volume is filled orders, counted once per trade, in cents of that market's
own collateral token.

Event	Volume	Counts as a trader
direct_buy_fill	price * amount	yes
direct_sell_fill	no, it is the same trade	yes
mint_fill, burn_fill	100 * amount, YES row only	yes
split_placed	no, it is a deposit	no
placements, cancels, amends, settled	no	no

Three points that are easy to get wrong:

• Mint and burn are volume. They are two-party matches, not protocol
  operations. Both sides agreed complementary prices, and the match settles by
  creating or destroying a pair rather than transferring existing shares. They
  are also the arbitrage boundaries that hold YES + NO near 100.
• split_placed is not volume. It is unilateral: collateral in, pair out,
  no counterparty and no agreed price. It moves open interest.
• The line is mechanically checkable. Every fill carries a
  counterparty_id and nothing else does, so the allowlist can be validated
  against the data rather than trusted.
• Use an allowlist over event types, never a denylist. A denylist silently
  admits any event type added later.

Volume is always per bridge and never summed across bridges. Base units
differ between collateral tokens by orders of magnitude, and this layer holds no
price feed to reconcile them. Unique traders, unlike volume, are comparable
across bridges.

Retention is the hard part

ob_order_events is ephemeral. trim_order_events() deletes rows once they
have been indexed, so the node holds a rolling window rather than full history.

A naive implementation returns 0 for a market whose events were trimmed, which
is indistinguishable from a market that genuinely had no activity. That silent
wrong answer is worse than a refusal.

Options worth weighing: return the coverage boundary alongside the figure so the
caller can tell truncated from empty, or ERROR when the requested range
precedes the earliest retained block. Either way the action should be explicit
that it answers for the retained window, not for all time.

Indexing

ob_order_events is indexed on id and block_height, but not on query_id.
A per-market aggregate is therefore a sequential scan today.

This is a public read that any caller can invoke and that every node executes,
so it should not be shipped without an index on (query_id, block_timestamp).
That index is cheap and helps the existing event queries as well.

Consistency with other implementations

The same metric is defined elsewhere in the stack. Wherever it is restated, the
implementations should be tested in lockstep against a shared fixture, meaning a
pinned market with a known figure, rather than each being retyped and trusted.

Out of scope

• Historical metrics beyond the retained window. That belongs to a service that
  keeps its own copy.
• Protocol wide totals across bridges.
• Charting endpoints and bucketed series.

Question

Is per bucket the right granularity, or should the action take a stream and a
settle time and aggregate the ladder itself? Per bucket matches the existing
query actions and keeps the signature stable if the ladder shape changes, at the
cost of callers making several calls.

Deadline

ETA: undefined

[holdex]

@vinarmani, please specify ETA (format ETA: DD-MMM-YYYY) in the Goal description. Example:

ETA: 20-Sep-2026

You can also set ETA via comment. Example:

@holdex goal set-eta 20-Sep-2026

or

@holdex goal set-eta +5d

See available commands to help comply with our Guidelines.

[vinarmani]

Two findings from building the same aggregate outside the node

Built this analysis as plain SQL against a synced mainnet node to see what the
action would be doing. Two things came out that are worth folding into the spec.

1. ob_order_events has no index on block_timestamp

Indexes today are on id and block_height. Every time-windowed query is
therefore a sequential scan:

EXPLAIN ANALYZE
SELECT query_id, count(*) FROM main.ob_order_events
WHERE block_timestamp > extract(epoch FROM now())::INT8 - 86400*7 GROUP BY 1;

->  Parallel Seq Scan on ob_order_events   Execution Time: 34.8 ms   (120,226 rows)

35 ms at 120k rows is nothing. The problem is that it scales with total
events, not with the size of the window requested. A one-hour window costs the
same as a one-year window, and both get slower as the table grows.

That is the wrong shape for $from_ts/$to_ts parameters, and it is worse for
the charting series, which issues many windowed reads.

(query_id, block_timestamp) serves the per-market case. If protocol-wide
windowed reads are also wanted, a leading block_timestamp index would serve
range scans better. Worth deciding which access pattern the action is really
for before choosing the column order.

2. Streams are keyed by (data_provider, stream_id), and the collision is real

main.streams is keyed on the composite, and 564 stream_ids are currently
shared across more than one provider on mainnet:

SELECT count(*) FROM (
  SELECT stream_id FROM main.streams GROUP BY stream_id HAVING count(*) > 1) x;
-- 564

Any implementation that resolves a stream by stream_id alone can silently read
a different provider's data. It will not error. It will return a plausible
number.

Our own scan had exactly this bug. It happened to be correct only because no
stream_id used by a live market currently collides, which is luck rather than
design, and it would start returning wrong answers the day a market is created
on a colliding id.

Both halves of the key are already in query_components:

data_provider : ABI word 0, address in the low 20 bytes -> hex chars 25..64
stream_id     : ABI word 1, ASCII NUL-padded            -> hex chars 65..128

Verified against contractsapi.DecodeMarketData on mainnet order book 1176:
hex extraction gives 0x1566e5dad82127d2193a504100ec16c17407f3da, the SDK gives
the same address checksummed.

Why these two interact

main.streams also has no index on stream_id, so each text lookup is a
parallel sequential scan over ~260k rows, about 21 ms. Resolving the stream once
per market rather than once overall made our scan O(markets x streams).

Fixing the key and hoisting the resolution were the same change, and together
took the scan from 2.6 s to 0.285 s, a 9x improvement on 167 live markets.
The gain grows with market count, since the per-market term disappears.

If the action resolves a stream at all, resolve the composite once and join on
streams.id thereafter.