Materialized Counters

Define a named running aggregate — sum, count, min, max, or avg — once and read it back in O(1) at any scale. SapixDB maintains the value incrementally on every matching write, so reading it never touches the strand.

When to use counters vs. aggregate queries

The aggregate query type computes the exact answer every time you call it, but it scans every record in the agent. That is fast for small agents and convenient for ad-hoc analytics, but it scales linearly with history.

A materialized counter shifts the work to the write path. Every write that matches the counter's optional filter applies a one-key update. Reads are a single RocksDB lookup — constant time forever.

Defining a counter

Send a POST to /v1/agents/:id/counterswith the field to aggregate, the function, and an optional filter. SapixDB immediately starts a background backfill that scans the agent's existing records and computes the initial value. While backfill runs, the counter is invisible to reads; once backfill completes, it becomes live and all future writes update it automatically.

POST /v1/agents/wallet/counters
Authorization: Bearer spx_root_YOUR_ROOT_KEY
Content-Type: application/json

{
  "name":   "balance",
  "field":  "amount",
  "fn":     "sum",
  "filter": {
    "field": "type",
    "op":    "in",
    "value": ["deposit", "credit"]
  }
}

{ "name": "balance", "status": "building" }

Reading a counter

GET /v1/agents/wallet/counters/balance
Authorization: Bearer spx_root_YOUR_ROOT_KEY

{
  "name":           "balance",
  "field":          "amount",
  "fn":             "sum",
  "filter":         { "field": "type", "op": "in", "value": ["deposit", "credit"] },
  "value":          1_234.50,
  "created_at_ms":  1756224000000,
  "updated_at_ms":  1756224891234
}

value is the current aggregate. updated_at_ms is the wall-clock time of the last write that changed it.

Listing counters

GET /v1/agents/wallet/counters
Authorization: Bearer spx_root_YOUR_ROOT_KEY

{
  "counters": [
    { "name": "balance",    "fn": "sum",   "value": 1234.50, ... },
    { "name": "tx_count",   "fn": "count", "value": 42,      ... },
    { "name": "max_single", "fn": "max",   "value": 500.00,  ... }
  ]
}

Only counters that have completed backfill appear in this list. A counter that is still building does not appear here but will return 202 on a direct GET /v1/agents/:id/counters/:name call.

Deleting a counter

DELETE /v1/agents/wallet/counters/balance
Authorization: Bearer spx_root_YOUR_ROOT_KEY

Returns 204 No Content. The definition and the stored value are removed from disk immediately. The strand records that fed the counter are not touched.

Supported functions

Filtering

The optional filter field uses the same SaQL FilterExpr syntax as scan queries. Only records that pass the filter contribute to the counter. You can use AND, OR, NOT, and all operators (eq, gt, in, contains, …).

{
  "name":   "paid_orders",
  "field":  "amount",
  "fn":     "count",
  "filter": { "field": "status", "op": "eq", "value": "paid" }
}

{
  "name":   "arcade_high_score",
  "field":  "score",
  "fn":     "max",
  "filter": {
    "AND": [
      { "field": "mode",  "op": "eq", "value": "arcade" },
      { "field": "valid", "op": "eq", "value": true }
    ]
  }
}

Nested fields

Use dot notation to reach into nested payload objects. "field": "order.total" extracts payload.order.total from each record.

{
  "name":  "gross_revenue",
  "field": "order.total",
  "fn":    "sum"
}

HTTP API Reference

Define request body

Authentication and Scopes

Counter management is an agent-configuration operation. It requires an API key with the write:agents/:id scope to define or delete a counter, and read:agents/:id to read one. A root key always has access.

If the agent has a row policy configured and the caller is JWT-authenticated, reading a counter no longer returns the raw materialized value — a counter is a running total over everywrite regardless of who made it, so SapixDB recomputes it live, applying the caller's row policy on top of the counter's own filter, instead of returning a value that could include rows the caller shouldn't see. Root/service keys (no JWT) keep the O(1) materialized path unchanged.