Why derived tables belong in the database

Every product has a table it wishes it had: the balance per account, the volume per token, the number of events per user over the last hour. Nobody writes that table by hand. It is derived from the tables that are written, and the question is where the derivation runs. The answer decides whether the number you read is exact or merely recent.

The usual answer is outside the database. Changes leave the store through a log, a stream processor folds them, and the result lands in a second store that serves reads. It works, and it costs you a reconciliation step forever: the derived table is a copy, computed by a system that did not see the commit, and the only way to know it is right is to compare it with its source.

NYXDB puts the derivation inside the engine. You describe the table you need as a query, and a transformation keeps it current from every committed write. The result is an ordinary table you read like any other, and it equals its definition over its sources at every point.

A query that becomes a writer

A transformation is a named writer that the catalog owns. It reads one or more relations, applies its body, which filters, computes, groups, windows and joins its sources, and writes the output rows into a target: a table, or one attribute group of it. Output columns bind to target columns by name, so you rename an output with AS where the names differ.

CREATE TRANSFORMATION balance_activity INTO balances ATTRIBUTE GROUP activity AS
SELECT
  chain_id,
  recipient AS address,
  token,
  COUNT(*) AS transfer_count,
  MAX(transferred_at) AS last_transfer_at,
  MAX(_index) AS activity_index
FROM transfers
GROUP BY chain_id, recipient, token;

From the moment this statement runs, every transfer that commits updates the recipient’s activity group in balances, including after the process restarts. A grouped transformation keeps one output row per group and updates it as source rows arrive, change and disappear. The usual aggregates are there, counts, sums, minimums, maximums, averages and distinct counts, and an output may be arithmetic over them, such as a weighted mean.

Fed by the same publication as everything else

Inside the engine, every committed write to a table tablet is published once, as an immutable batch on that tablet’s committed feed. The docs call this contract PSI. Transformations, materialized views, indexes and streaming queries all subscribe to that one publication; there is no second delivery path with its own semantics. A transformation receives each committed change once and applies it once.

Each subscriber keeps its own durable cursor and acknowledges only what it has applied. Producers never wait on consumers, so a slow transformation never holds back a write. After a restart, a transformation resumes from its cursor; if that cursor is no longer covered by the retained feed, it reseeds explicitly from a snapshot. It never skips a change silently.

This is the difference between a derived table and a copy. A copy is maintained by a process that observes the source from the outside and has to be checked against it. A transformation is maintained by the same commits that change the source, so the check is built in.

Chains, and reading what you write

The target of a transformation is an ordinary table, so another transformation can read it. A change to the first source flows through every link of the chain, each link matched on the table and attribute group it reads.

CREATE TRANSFORMATION transfer_value_lookup INTO transfer_values AS
SELECT
  t.chain_id AS chain_id,
  t.token AS token,
  t._index AS _index,
  t.amount AS amount,
  p.price_usd AS price_usd
FROM transfers t
LOOKUP JOIN token_prices p ON p.chain_id = t.chain_id AND p.token = t.token;

transfer_values is then a source for the next transformation, for example a fold of USD volume per token. Lineage is tracked per attribute group, which is what lets a transformation read a table and write one group of that same table: the engine never delivers the transformation’s own writes back to it, and it checks at creation that no chain of transformations loops back through the groups it reads.

What this removes from your stack

There is no stream processor to deploy and no second store to keep in step. A derived table is created with one statement. Redefining it with CREATE OR REPLACE TRANSFORMATION installs a new revision and reseeds it, and dropping it removes the writer while leaving the target table and its rows in place.

You can watch it work. system.transformations lists each transformation by name and identity. Each revision reports its source position, the rows it has written, its completed rows, its condition and its health. And the lineage relation shows the source, the transformation and the target of every live revision, so the path from a write to the table it feeds is a query away.

The transformations concept page covers the resume shapes after a restart and the system relations in full, the PSI page describes the publication every subscriber reads, and the statement reference has the complete grammar.