Cache results on the client

Caching is advertised, not requested. The worker attaches vgi.cache.* metadata to the batches it emits, and the client — the DuckDB extension — decides what to do with it. Nothing is cached unless you say so, which is the right default for a function that might not be pure.

The whole worker

cache.ts

// Copyright 2025, 2026 Query Farm LLC - https://query.farm

// cache is the result-caching example for the vgi-typescript documentation.
//
// Caching is advertised, not requested: the worker attaches vgi.cache.*
// metadata to the FIRST data batch it emits, and the client (the DuckDB
// extension) decides what to do with it. Nothing is cached unless you say so.
//
// This worker exposes rates(), standing in for a slow upstream whose answer is
// worth reusing, and shows the whole vocabulary: a freshness lifetime, a
// validator plus revalidatable so the client can ask "still good?" instead of
// paying for a recompute, and the 304-equivalent reply to such a request.
//
//   bun run cache.ts
//   # then, in a Haybarn shell:
//   ATTACH 'rates' (TYPE vgi, LOCATION 'bun run /abs/path/cache.ts');
//   SELECT * FROM rates.rates();   -- repeat calls inside the TTL never land here
//   SELECT hits, misses, inserts FROM vgi_result_cache_stats();
//   SELECT * FROM rates.upstream_calls();   -- proves the worker was not re-run

import {
  Worker,
  defineTableFunction,
  batchFromColumns,
  cacheControlMetadata,
  toSchema,
  int,
  str,
} from "@query-farm/vgi";

const ratesSchema = toSchema({ pair: str, rate: int });

// A strong validator for the payload below. Anything opaque and stable works —
// a content hash, a database version, an upstream ETag — as long as it changes
// exactly when the payload does.
const ETAG = '"rates-v1"';

const TTL_SECONDS = 300;

// Counts real invocations so the caching can be observed rather than assumed.
let calls = 0;

export const rates = defineTableFunction({
  name: "rates",
  description: "Exchange rates from a slow upstream, cached on the client",

  onBind: () => ({ outputSchema: ratesSchema }),
  initialState: () => ({ emitted: false }),

  process: (params, state, out) => {
    if (state.emitted) return out.finish();
    state.emitted = true;

    // A conditional request: the client already has a payload and is asking
    // whether it is still good. Answering costs nothing here, which is exactly
    // when `revalidatable` is worth advertising.
    if (params.ifNoneMatch === ETAG) {
      out.emit(
        batchFromColumns({ pair: [], rate: [] }, ratesSchema),
        // A zero-row batch carrying notModified is the 304 equivalent: keep
        // what you have. The client re-uses its stored rows without a restream.
        cacheControlMetadata({
          notModified: true,
          ttl: TTL_SECONDS,
          etag: ETAG,
          revalidatable: true,
        }),
      );
      return;
    }

    calls++;
    out.emit(
      batchFromColumns(
        { pair: ["EURUSD", "GBPUSD", "USDJPY"], rate: [108n, 127n, 15700n] },
        ratesSchema,
      ),
      // Metadata rides the FIRST data batch. It cannot go on the schema — the
      // IPC stream fixes that when the stream opens, before this runs.
      cacheControlMetadata({
        ttl: TTL_SECONDS,
        etag: ETAG,
        revalidatable: true,
        // Grace windows: serve stale immediately while refreshing in the
        // background, and keep serving stale if a refresh RPC fails.
        staleWhileRevalidate: 60,
        staleIfError: 3600,
      }),
    );
  },
});

// Reports how many times the upstream was actually hit, so a query can prove
// the cache engaged rather than take it on faith.
export const upstreamCalls = defineTableFunction({
  name: "upstream_calls",
  description: "How many times rates() actually computed a result",
  onBind: () => ({ outputSchema: toSchema({ calls: int }) }),
  initialState: () => ({ emitted: false }),
  process: (_params, state, out) => {
    if (state.emitted) return out.finish();
    state.emitted = true;
    out.emit(batchFromColumns({ calls: [BigInt(calls)] }, toSchema({ calls: int })));
  },
});

export const worker = new Worker({
  catalog: {
    name: "rates",
    comment: "Documentation example: advertising a cacheable result",
    schemas: [{ name: "main", functions: [rates, upstreamCalls] }],
  },
});

if (import.meta.main) worker.run();

Metadata rides the first data batch

out.emit(
batchFromColumns({ pair: [...], rate: [...] }, ratesSchema),
cacheControlMetadata({ ttl: 300, etag: '"rates-v1"', revalidatable: true }),
);

It cannot go on the schema. The IPC stream fixes the schema when the stream opens, before process has produced anything — so batch metadata is the only channel that exists at the point where you know what you are returning.

Emit it on the first data batch. Later batches carrying it is not an error, but nothing reads them.

Freshness

Field	Meaning
`ttl`	Lifetime in seconds from full-result receipt. Skew-immune, and it wins over `expires`.
`expires`	An absolute RFC 3339 UTC deadline.
`noStore`	Explicit “never cache”. Overrides any freshness key.
`scope`	`“catalog”` (default) or `“transaction”` — reused only inside the transaction that produced it.
`staleWhileRevalidate`	Grace window to serve stale immediately while refreshing in the background.
`staleIfError`	Grace window to keep serving stale when a refresh fails.

Presence of ttl or expires is what makes a result cacheable at all.

Validators, and the 304 equivalent

etag plus revalidatable: true tells the client it may ask “is this still good?” instead of paying for a recompute. The question arrives as params.ifNoneMatch:

if (params.ifNoneMatch === ETAG) {
out.emit(
  batchFromColumns({ pair: [], rate: [] }, ratesSchema),
  cacheControlMetadata({ notModified: true, ttl: TTL_SECONDS, etag: ETAG, revalidatable: true }),
);
return;
}

A zero-row batch carrying notModified is the 304: keep what you have. The client reuses its stored rows without a restream.

Only advertise `revalidatable` if answering is genuinely cheap

The flag is what gates whether the client ever sends a conditional request at all. Setting it on a function whose freshness check costs as much as recomputing turns one round trip into two.

Prove it engaged

Guessing is not good enough here, and the worker itself can settle it. cache.ts counts real invocations:

SELECT count(*) FROM rates.rates();
SELECT count(*) FROM rates.rates();
SELECT count(*) FROM rates.rates();
SELECT count(*) FROM rates.rates();

SELECT hits, misses, inserts, entries FROM vgi_result_cache_stats();
SELECT * FROM rates.upstream_calls();

Output

hits	misses	inserts	entries
3	2	1	1

Output

calls
1

Four queries, one upstream call. That second result is the one that matters: it is measured inside the worker, so it cannot be explained away by the extension’s own bookkeeping.

Per-value memoization

perValue: true additionally memoizes each distinct input tuple’s output — for a scalar, or a blended table-in-out called through a correlated LATERAL.

Off by default, and it should usually stay off

A per-value serve is not free: the client pays a key probe, a decode, and a per-value assembly step, and that only pays back when it costs less than the worker call it replaces. For a cheap map — arithmetic, a string tweak, a lookup in memory — the engine measures it at roughly 50× slower than just calling the worker.

Turn it on when a single call is genuinely heavy and repeats across rows: a model inference, a geocode, a rate-limited HTTP fetch. Only you know which side of that line your function is on, which is why the engine will not guess.

Next steps

Every field → Cache control.
Caching a whole table → Expose a catalog.