@jarenjs/db

For existing-file adoption with native queries, receipts and jobs, start with the combined public recipe and evidence ledger. For build-selected SQLite/PostgreSQL applications, see the backend capability and recovery matrix. Adopted-trigger capture and physical keysets retain their documented refusals; synthetic proof does not retire downstream SQL.

Documents and entities in SQLite and PostgreSQL. A model document declares collections (a JSON Schema, a key, indexes) and — since phase B — entities: keys, typed columns, relations, defaults and an optimistic-concurrency token, all inside the schema through the x-entity vocabulary. openStore applies the physical mapping through a dialect and gives transactional, schema-validated reads and writes; queries arrive as plain Jaren query documents (usually written through @jarenjs/linq) and are pushed down to SQL where equivalence is proven, with everything else running honestly in the engine. The same code runs on Node, on Bun, and in a browser against an injected wasm handle, with zero dependencies outside @jarenjs/*.

The honest framing, first: Prisma, Drizzle and Kysely are mature, support several databases, and are faster on some benchmark rows — those losses are published on the suite page with their reasons. What none of them has is a query that is one serializable JSON document, executable by two independent engines proven to agree by a differential oracle, running unchanged in Node, Bun and the browser, with schema-validated writes from the fastest validator in the ecosystem. The composition is the product; the individual numbers are what they are.

import { openStore } from '@jarenjs/db';
import { nodeDriver } from '@jarenjs/db/node';

const store = await openStore({
  $model: '0.1',
  collections: {
    users: {
      schema: {
        type: 'object',
        required: ['id', 'email'],
        properties: {
          id: { type: 'string' },
          email: { type: 'string', format: 'email' },
          age: { type: 'integer' },
        },
      },
      key: '/id',
      indexes: [{ name: 'by_age', path: '$.age' }],
    },
  },
}, { driver: nodeDriver(), path: 'app.db' });

const users = store.collection('users');
await users.insert({ id: 'u1', email: '[email protected]', age: 36 });

// a query document — here written by hand; linq writes the same one,
// its source wrapped as ['$[*]'] so an item that is an array stays one item
const adults = await users.execute({
  $for: { it: '$[*]' },
  $where: { $ge: ['$it.age', 21] },
  $return: '$it',
});

Or, by code — the same document, written by the model pen (MODEL-PEN.md) and typed without a generate step (InferMeta<typeof model> binds the typed store):

import * as m from '@jarenjs/linq/model';

const model = m.defineModel({
  collections: {
    users: m.collection(
      m.object({ id: m.string(), email: m.string().email(), age: m.integer().optional() }).open(),
      { key: (u) => u.id, indexes: [m.index((u) => u.age)] },
    ),
  },
});
const store = await openStore(model, { driver: nodeDriver(), path: 'app.db' });

Over an entities model, an entity set is a provider too (MODEL-FORMAT §10.1): a chain binds through the set's root and the store runs the document whole — the translator for a bare-binding selection or equijoin, the declared residual for a projection — while the store itself, serving several roots, is refused by name (JL0007):

import { from, fromAsync } from '@jarenjs/linq';

const starred = from(store.sync.entity('Post')).where((p) => p.stars.ge(3));
starred.toDocument();   // { $for: { it: '$.Post[*]' }, $where: { $ge: ['$it.stars', 3] }, $return: '$it' }
starred.toArray();      // the entity translator, one statement
await fromAsync(store.entity('Post'))
  .join(fromAsync(store.entity('User')), (p) => p.authorId, (u) => u.id, (p) => p)
  .toArray();           // a two-root equijoin, one statement

A declared relation navigates on the chain, and the document still carries no relation name: the chain reads the set's relation table (store.entity('Post').relations, MODEL-FORMAT §10.1) and lowers p.author.email to the correlated phrase the engine and the store both run. The store answers it as the residual it is — explain() says so, strict refuses it — over the two fetched roots, never a statement per row:

const byAuthor = from(store.sync.entity('Post'))
  .where((p) => p.stars.ge(3))
  .select((p) => ({ title: p.title, by: p.author.email }));

byAuthor.toDocument();
// { $for: { it: '$.Post[*]' },
//   $where: { $ge: ['$it.stars', 3] },
//   $return: { title: '$it.title',
//              by: { $for: { r1: '$.User[*]' },
//                    $where: { $eq: ['$r1.id', '$it.authorId'] },
//                    $return: '$r1.email' } } }
byAuthor.explain().hops;   // [{ member: 'author', kind: 'oneToOne', binding: 'r1' }]
store.sync.explain(byAuthor.toDocument());
// { mode: 'set', referenced: ['Post', 'User'], sql: null,
//   reasons: [{ construct: '$return',
//               reason: 'entity queries return one bare binding natively; projections run in the engine' }], … }
byAuthor.toArray();        // the rows, two fetches — one per referenced root

from(store.sync.entity('User')).where((u) => u.posts.all().count().ge(2));
// … $where: { $ge: [{ $count: { $for: { r1: '$.Post[*]' },
//                                $where: { $eq: ['$r1.authorId', '$it.id'] },
//                                $return: '$r1' } }, 2] } …

A many-to-many member (u.labels) is refused at build time (JL0105) naming the join table: it is not a queryable root in this version, so load({ include: { labels: true } }) is how the memberships are read.

execute answers in the ENGINE's result shape (QUERY-FORMAT §1, "singleton ≡ item"): undefined for no rows, the document itself for exactly one, an array for more — typed SequenceResult<R>, with R stated per call (users.execute<User>(…)) because only the caller knows what its $return produces. query() answers the same document as an item cursor (for await), one item per pull and never unwrapped — the read to use when an item may itself be an array. The handle's own shape binds at store.collection<User>('users'). An entity set answers the same cursor as cursor(document, options): one row per pull from an open statement, the statement released exactly once when the loop breaks, throws, finishes or its signal aborts (JD2072 on the next pull). Every cursor says what it will do — streaming: 'row', or 'buffered' with the barrier that forces it (a set residual's construct, an external the database cannot bind, a chain's window) — and a linq chain's for await over a set IS this cursor, so a break after three rows of twenty thousand costs three rows. A cursor registers no snapshots unless asked (tracking: true, one per yielded row — unbounded in the result size, by the caller's choice).

On synchronous connections, store.sync.entity(name) also offers cursor, loadCursor and page. The cursors use for...of and support return() and Symbol.dispose; pages return values immediately. Both surfaces use the same plans, bounds and continuation identities. Closing the store releases every active iterator; later entry refuses with JD2063.

A graph streams the same way: loadCursor(spec) yields one root with its includes attached, and every include is bounded per root (maxRows, maxBytes, defaults 1000 rows and 1 MiB; Infinity spelled for the unbounded case) — crossing a bound is JD2073 naming the root, the member and the bound, never a silently truncated graph. A list pages over a composite keyset — entity.page(spec, { limit, after, maxBytes }) with orderBy over (updatedAt, id)-shaped orderings, the primary key appended as the tie-breaker and null placement matching the plan — and answers { items, continuation, hasMore, snapshot }: the continuation is unsigned and structural (the host signs it), an item larger than maxBytes is JD2074 without advancing it, and snapshot is true only over an immutable ordering; over a mutable one the page is live and says so (MODEL-FORMAT §10.5). explainLoad() reports both facts separately: order is the deterministic order the statement executes under in EVERY load mode — the declared terms and the tie-breaker the clause appends, the primary key in keyset mode and the row identity otherwise — and identity is the ordering identity a continuation carries and is checked against, null for a load that has no continuation to emit.

• The pushdown planner with explain(). A query compiles through the engine's published AST into a dialect-neutral plan and renders to guarded, parameter-bound SQL; whatever cannot be proven equivalent runs as a real compiled Jaren query (the residual), and explain() always says which is which — the SQL, the bound parameters, the indexes used (verified against the database's own plan output), and the residual's named reasons. It answers for the RUN it describes: given the externals execute is given, a value the database cannot bind (a boolean, a null, a missing name) is reported as the set residual the call becomes — mode, statement and reason — and counted in stats().bind.diverted, so a production diversion is visible where nobody calls explain(). Every explanation carries streaming ('row' or 'buffered') and barrier (the construct that forces a buffer, or null), the same classification the cursor itself carries; strictStreaming: true on a cursor declines a buffering plan by name (JD0037) before any statement runs. Every explanation also carries order: the deterministic order the statement executes under, in one closed vocabulary — a mapped column, a document path, a bucketed plan's group key, or the row identity the plan appends so a sequence answers in insertion order — read from the plan rather than parsed back out of SQL, and null only for a statement that orders nothing at all (an aggregate answers one row; a k-nearest fetch is unordered because the engine ranks it). Each refusal reason is drawn from a closed vocabulary too, so a reason a caller matched on stays the sentence it was. A $return that is ONE member path over the binding projects that path into the statement — its value beside its JSON type, so a present null, an absent member and a boolean read back exactly as the engine answers them — and $count over it counts the rows where the member is present with a COUNT(*). A $return that is a nested SHAPE — objects, arrays, literals and member paths, to any depth — projects the same way: the statement fetches one value/type pair per DISTINCT leaf (a path named twice is fetched once) and the decoder rebuilds the shape, so an absent member is omitted from its object and skipped in its array exactly as the engine does it, and a literal null stays present where a path that finds nothing does not. explain().projection names the path or the leaf paths, and reading a shape no longer reads every document. A shape the tree cannot rebuild — an operator over a member, a reference to the whole entity binding, a non-singular path — refuses WHOLE and runs per row, with explain().residualProjection naming what stayed behind: promoting the half that composes would answer a shape nobody asked for. Every explain() also carries budget: the profile that applied, every bound it imposed, and the two driver slots (time, estimatedRows) reported unavailable on SQLite rather than estimated (MODEL-FORMAT §8). A differential oracle — a committed corpus and a seeded generator, every case run resident, native, native again over a store with every declared index removed, and forced-residual — keeps every path agreeing, with the one arithmetic deviation declared rather than hidden (MODEL-FORMAT §10.6: SQLite's compensated SUM and the engine's naive one differ in the last bit). strict: true turns any residual into a compile error. Constant trees fetch only a row marker. Counts over entity projection trees lower too; a single-path count or window excludes absent members before counting items. A window over a projection that can emit several items runs in the engine.

• Registered operators, correct in the residual, pushed where it pays. Open with a registry (operators: createJsltRegistry().use(mathPack).use(financePack)) and a query may use $npv, $mean, $sqrt, … over stored documents. Each runs correctly in the residual — identical to the in-memory engine, explain() names it. The pushable:'scalar' subset (the math ops) is additionally accelerated into SQLite as deterministic UDFs where the driver allows (node:sqlite yes; bun:sqlite has no UDF API and stays the residual — reported by capabilities.pushableOperators). Pushdown is a large win beside a selective native predicate or a LIMIT (3.9×, 615× measured) and a wash on a solo full-table computed predicate — published honestly in MODEL-FORMAT §8.2, not gated behind a cost model SQLite gives no row estimates to build. A first-class operator is allowed under a profile by default (which also blocks host-side UDF registration for untrusted documents); the profile's functions allow-list still governs a $call-reached fn; the row bound still fires. Without a registry the store is unchanged — $npv is JQ0002. A pack may also mark a whole-sequence summary pushable: 'aggregate' (the statistics pack's $mean, $median, $variance, $stddev): where the driver has an aggregate API it becomes a registered SQL aggregate over the member's column, folded by the same pure function, so the accumulation runs in SQLite rather than over fetched rows. The token is a promise the store checks — one seq<number> operand and a scalar result, or the declaration is refused at openStore by name — and the promotion still needs a numeric member the schema forbids null on, because SQL cannot tell a stored null from an absent one and the engine can.

• Typed path comparisons and distinct scalar projections. Comparisons between two paths in the same non-null number/string family lower without a UDF. $distinct over a typed scalar projection groups by its value and JSON type, excluding missing members. Unordered inputs retain first occurrence; ordering solely by the projected path also lowers. Literal windows apply to the distinct items.

• Grouping and joins lower whole, or not at all. A $groupby over schema-typed member keys becomes a real GROUP BY: the keys come back with their JSON types beside them, so a group whose key is ABSENT leaves that member out exactly as the object constructor does, the closed aggregate set ($count, $sum, $avg, $min, $max) folds in SQL under the ENGINE's empty rules (0 for a count or a sum, no member at all for the other three), and the groups come out in the engine's own order of first appearance unless an $orderby over the keys or count/min/max says otherwise. Entity queries join any number of bindings: every binding past the first must be attached by a column equality to one already joined, which is what makes the plan a nested loop the engine can be compared against — a binding nothing attaches would be a cartesian product, so it is the residual, named, and strict: true refuses it. explain() lists the join order with the equalities that attached each binding, and the group's keys, aggregates and order. Nullable scalar keys also group; ordering null or boolean keys remains residual to preserve the engine's error. Windows over singleton group constructors lower, and counts over constructors containing only keys, literals and row counts count the grouped subquery. Ties under a partial group ordering retain first appearance. Boolean predicates may span joined bindings when each leaf belongs to one binding; the equality graph still establishes every join. A join predicate that is not an equality — a range between two mapped columns of one family — refines a match it never makes: the anchor is still an equality, so a range alone stays the residual. A projected join lowers too, through the same leaf decoder a single-binding projection uses, each leaf read from its own binding's alias.

• A declared join table is a read-only query root. $.<JoinTable>[*] answers rows carrying exactly its two key columns and joins to the entities it relates, so Person → Person_Tag → Tag is one statement over three roots — while store.entity('<JoinTable>') is still JD2004 and memberships are still written through link/unlink. Because the root exists, a many-to-many hop on the chain lowers through it (two links: the membership, then the row it names) instead of refusing.

• Storage is declarative. Indexed paths become generated columns plus real indexes, typed from the collection's schema. Opening an existing database verifies the declared shape and refuses to alter it — reshaping is the migration story.

• Spatial members get indexable columns. A position is an array and a geometry is an object, so neither is indexable as it stands. An index declaring derive: 'geohash' (with a required precision) or derive: 'bbox' materializes the cell, or the four box edges, as columns computed by @jarenjs/core/geo — a generated column over a registered deterministic function where the driver can index one, and a stored column the store writes where it cannot.

• A spatial query is two stages, and explain() names both. A $within, a $bbox-intersects, a bounded $distance or a geohash probe over such a collection narrows in SQLite through the index and refines in the engine. $bbox-intersects and a geohash cell prefix test are exact and need no refinement; neighbourhood membership retains unbounded candidates and preserves the engine's empty-item error. $within and a bounded $distance push a bounding box the truth table proves they imply, and the exact predicate re-runs over the narrowed candidates — explain().prefilters says which, over what columns, and whether it decided or merely narrowed. The worked example, the geofence and the measured numbers are in Spatial storage below.

• A time series is a composite index, not a storage kind. Declare { "path": ["$.series", "$.at"] } over a numeric epoch member and the planner recognizes three shapes over it: a half-open range under a series equality, an as-of lookup (the index read backwards, one row), and a fixed-width bucket ladder — a $groupby over $time-bucket, or a $resample whose spec asks for nothing a GROUP BY cannot do — as integer arithmetic in SQL. A fill policy, a calendar width, a rolling window and an as-of JOIN are named refinements: the index bounds the fetch and @jarenjs/core/series decides, with explain().series carrying the reason code and the last run's actual candidate and result counts. See Time series.

• Migrations are documents. planMigration diffs two models into rendered-DDL + JSLT-transform + assertion steps; a shadow database replays the whole chain before the real store is touched; a checksummed history refuses edited or reordered migrations; a narrowing without an adequate transform is refused against the REAL data, inside the transaction. And by code: @jarenjs/linq/migration (MIGRATION-PEN.md) writes the same document with the data transform typed old row → new row, jaren-db loads model and migration MODULES beside JSON, plans from the committed model.snapshot.json, refuses a module that is not pure, and jaren-db check fails CI on a model that moved without a plan (MIGRATION-FORMAT §11).

• A document migration does not need the database. Its jslt and query steps act on documents, so migrateDocuments runs them over arrays and streamDocuments over a source it may walk only once, writing each document out as it finishes and holding one batch. Both share ONE implementation of what a step means with the store runner, so the array answer equals the store answer — on the same step, in the same words, down to the refusal. A step that needs tables (ddl, sql, rebuild, derive) is refused by name before the first document is read, never skipped (MIGRATION-FORMAT §6.1). jaren-db documents is the same thing at the command line, over a JSON array, JSONL or stdio:

  # rewrite a file in place — a sibling temporary is renamed over it
  # only once every document has survived every step
  jaren-db documents --migrations ./migrations --in users.jsonl --in-place --yes
  
  # the CI shape: transform and validate everything, write nothing
  jaren-db documents --migrations ./migrations --in users.json --check
  # 0 the chain applies, 1 the run failed, 2 the command line was wrong
  
  # also the converter: the input and output encodings are independent
  jaren-db documents --migrations ./migrations --in users.json --out users.jsonl

  A JSON array is scanned structurally rather than parsed whole, so both encodings hold one batch and not the file: the live set is flat in the size of the input, and a file larger than memory still migrates.

• The safe profile. Untrusted query documents run under composed bounds: engine limits on the residual, a mandatory row bound that refuses rather than truncates, reference allow-lists, optional full-scan refusal, per-collection mandatory predicates no document shape can shed, and a per-root MEMBER allow-list — the members a document may read, checked wherever it names one, where allowing a member allows what is under it and reading the item whole is refused rather than quietly narrowed. Read-only stores refuse writes at the driver.

• Writes validate through an injected hook; without one, store.capabilities.validated is false and the docs say what that costs. The public API is asynchronous (the browser's OPFS story forces it) with a promise-free store.sync twin where the driver is synchronous.

Spatial storage — the model, the plan, the fence, the numbers

A collection stores GeoJSON as it is — a position is an array, a geometry is an object, nothing is wrapped — and declares what to index over it:

const store = await openStore({
  $model: '0.1',
  collections: {
    places: {
      schema: {
        type: 'object',
        properties: {
          id: { type: 'string' },
          // typed as geography: a spatial predicate is only pushed onto a
          // member the schema types as an array or an object and nothing else
          at: { type: ['array', 'object'] },
        },
      },
      key: '/id',
      indexes: [
        { name: 'by_box', path: '$.at', derive: 'bbox' },
        { name: 'by_cell', path: '$.at', derive: 'geohash', precision: 6 },
      ],
    },
  },
}, { driver: nodeDriver() });

derive: 'bbox' materializes the member's bounding box as four columns and derive: 'geohash' its cell, both computed by @jarenjs/core/geo — generated columns over registered deterministic functions where the driver can index one, stored columns the store writes where it cannot (MODEL-FORMAT §§2.1, 3). The query a consumer writes then narrows in SQLite and refines in the engine, and explain() says so:

const places = store.collection('places');
const inside = {
  $for: { p: '$[*]' },
  $where: { $within: ['$p.at', '$region'] },
  $return: '$p',
};
await places.execute(inside, { externals: { region } });

const how = await places.explain(inside, { externals: { region } });
how.prefilters;
// [{ construct: '$within',
//    columns: ['gx_at_bbox_w', 'gx_at_bbox_e', 'gx_at_bbox_s', 'gx_at_bbox_n'],
//    exact: false }]
how.residual.reasons[0].reason;
// 'a bounding-box pre-filter is pushed; exact containment refines in the engine'
how.scanNarrative;
// 'SEARCH places USING INDEX places_by_box (gx_at_bbox_w>? AND gx_at_bbox_w<?); …'

The region arrives as a bound parameter: a GeoJSON object is not a value any database can bind, so what binds is one edge of its box per slot, computed at bind time from the same kernel the stored columns came from. $bbox-intersects and a geohash prefix no longer than the indexed cell are exact and need no refinement; neighbourhood membership refines to preserve errors on unbounded candidates. $within and a bounded $distance push the box they provably imply and re-run the exact predicate over the narrowed candidates. A circle that reaches a pole or crosses the antimeridian pushes nothing — there is no single box to push — and the answer is the same, reached by reading more rows. A proximity probe is nine cells ($geohash-neighbours), never one prefix: two points ten metres apart can differ in the first character of their cell, so a single-cell range is bucketing, not proximity.

The geofence. Register the same document as a live query and it is maintained as writes arrive — the initial fetch narrows through the index, and every touched row is re-evaluated by the engine's exact predicate:

const fence = await places.live([{
  $for: { p: '$[*]' },
  $where: { $within: ['$p.at', '$region'] },
  $return: '$p.id',
}], { externals: { region } });
fence.mode; // { strategy: 'rows', mode: 'incremental' }
fence.subscribe(({ patch }) => {
  // add    when a point enters the region
  // remove when it leaves
  // nothing while it moves within (a whole-document return sees a replace)
});

That is per-row evaluation, not an incremental spatial index: every write runs $within against the region once, on the store's connection, and a large region at a high write rate pays for it on every write (LIVE-FORMAT §7 states the cost). An ordering by $distance or a spatial aggregate re-runs on invalidation with the reason in live.mode — declared, never silent.

The numbers, the loss included. benchmark/spatial.js stores 50,000 points over the Netherlands and probes one box at 258 of 50,000 (0.5 %) selectivity, asserting every plan case of the committed spatial corpus and every timed shape against the JavaScript engine before a single timing is printed. The $within a consumer writes went from 80 ms as a full scan to 2 ms over the bbox index (40.0×); $bbox-intersects is 1.9 ms, a bounded $distance 1.5 ms; one geohash cell answers in 0.0068 ms for 0 row(s) and the honest nine-cell probe in 0.026 ms for 2 row(s). The row the store had to win is the same $within in the in-memory engine over the parsed array, no database at all: 32 ms. The indexed store is now 16.1× faster than it — but the un-indexed scan is not, and the comparison is not an even one either way: the engine starts from parsed objects where the store starts from bytes on a page and pays JSON materialisation for every row it returns. Both rows stay published. The deterministic-UDF hatch takes a literal $within on a collection with no derived index, and its profile is measured on the same rows in MODEL-FORMAT §8.2 (a loss as a sole predicate, a large win beside a selective conjunct or a LIMIT).

Two shapes on disk for one declaration. derive: 'bbox' has a second physical realization: physical: 'rtree' keeps the same four derived columns and stores the boxes in a SQLite R*Tree beside the collection, synced by three declared triggers, with no B-tree over the columns (MODEL-FORMAT §2.1). The logical model is unchanged — the spatial corpus runs every entry under both mappings, in all three executors, with no special-cased entry — and the pushed conjunct becomes a rowid subquery over the virtual table. Through the store the same $within measures 0.46 ms against 2 ms — 4.3× in the R*Tree's favour; loading the same rows costs 718 ms against 399 ms for 50,000 documents in one transaction — 1.8× the write cost, because the R*Tree is a second table written inside every write transaction. Isolated from the store on a raw connection the probe is 0.3 ms against 1.9 ms — 6.4× in the R*Tree's favour. Both halves are published because both are the price. One honest difference comes with it: an R*Tree stores 32-bit floats rounded outward, so its box is a superset and $bbox-intersects is refined rather than exact there — same rows, and strict: true says so.

One document, three executors, proven to agree. The same spatial query document runs in three places — the JavaScript engine (compileJsonQuery), SQLite through the Node driver, and SQLite compiled to wasm in a real browser tab — and one committed corpus holds all three to the same answers. test/json/fixtures/spatial-corpus.json records what the engine answers for every case (generated, never hand-typed); test/db/spatial-oracle.test.js runs every entry through the Node driver under all three mappings — the derived indexes as columns, the same indexes as R*Trees, and none; and packages/website/e2e/spatial-agreement.spec.js drives the data studio's Store pane to run the same entries through the wasm build in Chromium, Firefox and WebKit, asserting every answer against the fixture on disk and the number of entries run against the corpus. Each runner names the executor, the entry and the query when it disagrees. That is the whole claim — not faster than anyone, not PostGIS — and the browser leg's limit is stated with it: it proves execution, not durability. The separate host matrix proves OPFS and IndexedDB persistence and the visibly non-durable memory fallback.

No head-to-head rival, and saying so. Nothing else in JavaScript stores GeoJSON in SQLite from a JSON query document, so the suite invents none. The rivals to know about: MongoDB ($geoWithin, $near, a 2dsphere index) has a GeoJSON-native query document and a real spatial index, and runs on a server — no browser execution, and no second engine to agree with; DuckDB-wasm with spatial runs in a tab with a real index and the overlay operations this store refuses to build, and its query is SQL, not a document. Neither runs one document through three executors proven to agree, and neither validates ring closure in a schema.

Vector storage — the column, the cut, the price, the ceiling

A collection can declare that one member is an embedding, and the store keeps it as a packed column beside the document:

const store = await openStore({
  $model: '0.1',
  collections: {
    memories: {
      schema: {
        type: 'object',
        properties: {
          id: { type: 'string' },
          text: { type: 'string' },
          // typed `array` and nothing else: a column over a member that
          // may also be a string or null is a column that lies about
          // some documents. minItems/maxItems make a wrong-width write
          // a validation error instead of an unrankable row.
          embedding: { type: 'array', items: { type: 'number' },
            minItems: 768, maxItems: 768 },
        },
      },
      key: '/id',
      indexes: [{ name: 'by_vec', path: '$.embedding', derive: 'vector', dims: 768 }],
    },
  },
}, { driver: nodeDriver() });

derive: 'vector' materializes the member l2-normalized and packed as little-endian binary32 — 4·dims bytes, computed by @jarenjs/core/vector — into one stored column on every driver, with no B-tree over it and no registered function (MODEL-FORMAT §§2.1, 3.1). Nothing seeks a blob of floats, so the entry names a column rather than an index; and because the column is stored rather than generated, a plain SELECT, a backup or a foreign tool can read the table without registering anything — which is also what lets bun, whose SQLite binding has no function API at all, store and read the same bytes.

"The k most similar" is not a keyword. It is the query language's own ordering and window (QUERY-FORMAT §8.15) — $orderby on a $similarity key, descending, under a $subsequence:

const memories = store.collection('memories');
const nearest = {
  $subsequence: [{
    $for: { m: '$[*]' },
    $where: { $eq: ['$m.topic', 'deploys'] },
    $orderby: [{ $key: { $similarity: ['$m.embedding', '$q'] }, $dir: 'desc', $empty: 'least' },
      '$m.id'],
    $return: '$m',
  }, 0, 10],
};
await memories.execute(nearest, { externals: { q: probe } });

const how = await memories.explain(nearest, { externals: { q: probe } });
how.mode;                       // 'knn'
how.rank;                       // { column: 'gx_embedding_v768', dims: 768,
                                //   probe: { external: 'q' }, offset: 0, limit: 10,
                                //   margin: 1e-6, decides: 'engine' }
how.sql;                        // SELECT "rowid", "gx_embedding_v768" … WHERE …
                                // — no ORDER BY, no LIMIT, no similarity call
memories.stats().knn;           // { queries, rows, candidates, fullFetches, diverted }

The pushed $where narrows in SQL exactly as in any other mode; the statement then projects (row identity, packed column) and nothing else; the engine unpacks, scores and keeps every row within 1e-6 of the offset + limit-th best; the candidates' documents are fetched by identity, and the ORIGINAL document — its whole $orderby, its window, its $return — runs over exactly those. So the column cuts and the engine decides: ties break by the document's own secondary keys, offsets and nested windows compose for free, and the rows the column cannot rank still appear where $empty: 'least' puts them. strict: true refuses the shape with JD0010 naming the rank, because the ordering is engine work — the same honesty the spatial refinement gets. A probe of the wrong width, or one that is not an array of numbers, diverts to the residual, so a query can never quietly become an O(table) scan nobody counted (ARCHITECTURE, "The k-nearest plan"). A literal probe of the wrong width is refused at plan time and explain() says why; an external one is only knowable when it is bound, so the plan stays knn and the fallback is counted instead — stats().knn.diverted is that count, and a consumer who binds probes from a model should watch it.

Several declared widths, one compiled query. A member may carry more than one derive: 'vector' index — a 384-wide embedding beside a 768-wide one — and an external probe then binds the column its OWN width names. The plan carries one alternative per declared width and emits one statement per alternative, prepared once and kept with the plan; the bind picks exactly one. No CASE across the columns (that would read every one of them per row) and no statement per call (that would give up the prepared cache). explain().rank names the alternatives and, when the call was given its externals, the selected width — the probe is named, never printed. A width the model does not declare is the same counted diversion any unbindable probe is.

The numbers, the losses included. benchmark/vector.js measures one k-nearest query every physical way it can run — over 10,000 and 50,000 vectors at 384 and 768 dimensions, k = 10, the median of 10 probes — and asserts that every path returns the identical top-k, ids and order, on every probe before a single timing prints. The flagship row is the plan a consumer's own document runs, which measures 191 ms at 50,000 × 768:

| path (ms) | 10,000 × 384 | 10,000 × 768 | 50,000 × 384 | 50,000 × 768 | |---|---:|---:|---:|---:| | engine resident sweep (no database) | 3 | 5.8 | 15 | 28 | | the k-nearest plan (the store's own) | 21 | 30 | 127 | 191 | | raw fetch + engine sweep (the plan's statement) | 19 | 28 | 118 | 182 | | ORDER BY over a registered function | 17 | 26 | 113 | 158 | | JSON-doc sweep (no vector column) | 232 | 468 | — | — | | sqlite-vec | 3.3 | 6.9 | 17 | 35 |

The row the column exists to beat is the last one that has no column: the same query document over a collection that stores the embedding only inside the document costs 15.6× the plan at 10,000 × 768, because every row's vector is parsed out of JSON before it can be compared. The row the store cannot beat is the one with no database in it: the same top-k over a resident Float32Array is 28 ms, which the plan is 6.8× slower than. That comparison is not an even one and the direction is the point — the sweep starts from decoded floats in RAM and pays nothing for durability, for filters that compose with the ranking, or for a process that can restart — but it stays published, because a store that is worth its price should be able to say what the price is.

Both halves of the price. The column costs on the way in as well as saving on the way out: writing the same documents with the index costs 10.2 s against 4.9 s for 50,000 documents in one transaction — 2.1× the write cost, because every write pays a JSON round trip of the member plus a normalize and a pack. On disk one vector is 3,072 B packed against 16,141 B as a JSON number array inside the document — 5.3× smaller — smaller, but added, since the document still carries the member the column is derived from.

Pushing the rank into SQL, re-measured. A registered similarity function inside an ORDER BY … LIMIT k is the obvious alternative, and the suite measures it against the real column with the probe hoisted out of the per-row call: 158 ms against 182 ms at 50,000 × 768, and 0.87–0.95× the fetch-and-rank across the grid — rough parity on speed. The plan does not emit it, and after that measurement the reasons are not speed: bun has no user-function API, so a plan that needed one would exclude an executor outright; and an ordering decided in SQL cannot break a tie by the document's own secondary keys, which is what the three executors have to agree on.

The rival, and the ceiling. sqlite-vec is the extension built for exactly this, and it is measured rather than described: it answers the same probes in 35 ms against 191 ms at 50,000 × 768 — 5.5× in sqlite-vec's favour, out of a database 6.6× smaller that holds no documents, over 40 probes, no disagreements. It is a loadable native extension, which is the one thing this store will not require — it would exclude the wasm tab and stock bun, half the execution story — so the comparison is published as what it is: a faster engine you may prefer, and a dependency this one does not take. What neither of them is, is an approximate index. Exact brute force is linear in n · d, and the suite states the envelope as arithmetic rather than opinion: 5.142 ns per vector component — one query reaches 100 ms at about 24,000 vectors of 768 dimensions and one second at about 252,000. Past that this design is the wrong tool and no margin changes it; what lies beyond is an approximate index, and this store does not have one.

The labelled retrieval comparison also scores an optional storage adapter with approximate candidates against exact cosine on cached live embeddings. The AI ledger reports the adapter's algorithm, exhaustive flag and returned candidate count, validates candidate identity/vector shape, and re-scores candidates itself. The DB ledger recipe continues to normalize as an exact adapter. No ANN runtime dependency is added.

The benchmark decision and its measured limits are maintained by Tangle’s recall instruments. The generic store retains exact ranking; external ANN adapters must meet the declared oracle and lifecycle contract.

One document, three executors, proven to agree. As with the spatial family, the k-nearest shapes of a committed corpus (test/json/fixtures/vector-corpus.json) run through the JavaScript engine, SQLite through the Node driver, and a real wasm build — indexed and unindexed — and every entry must answer identically, including a deliberate one-binary32-ulp near-tie and the windows that reach past the scored rows into the tail the column cannot rank.

Time series — the index, the ladder, the refinement

The physical declaration is one a model already has:

{ "name": "by_series_at", "path": ["$.series", "$.at"] }

No derive kind, no column type, no host function, no extension. What the planner adds is the reading of that index — a B-tree seeks as far as its leading columns are decided, so a query that pins series with an equality and ranges over at is a SEARCH, and one that only bounds at is a scan the plan says so about.

// native: SEARCH sample USING INDEX sample_by_series_at (gx_series=? AND gx_at>? AND gx_at<?)
await sample.execute({
  $for: { s: '$[*]' },
  $where: { $and: [
    { $eq: ['$s.series', 'sensor-a'] },
    { $ge: ['$s.at', from] },
    { $lt: ['$s.at', to] },
  ] },
  $orderby: [{ $key: '$s.at' }],
  $return: '$s',
});

// native: the same index, GROUP BY over integer bucket arithmetic
await sample.execute({ $resample: [
  { $for: { s: '$[*]' }, $where: { $eq: ['$s.series', 'sensor-a'] }, $return: '$s' },
  { every: 'PT1M', aggregate: 'mean' },
] });

// hybrid: the index bounds the fetch, rollingSeries decides
await sample.execute({ $rolling: [
  { $for: { s: '$[*]' }, $where: { $eq: ['$s.series', 'sensor-a'] }, $return: '$s' },
  { width: 'PT1M', aggregate: 'mean', minPeriods: 30 },
] });

The numbers, the loss included. benchmark/series.js answers the same range, the same buckets, the same rolling window and the same as-of join over one seeded corpus by plain references, by the temporal kernel, by a generic query document, by hand-written SQL and by the store — every route checked against the others before a timing is taken. At 100,000 samples at 1-second spacing, Node v24.19.0, the store is measured three ways at once — the planned range costs 4.3× the hand-written statement and 1435.1× the resident cut, and the pushed bucket ladder 2.1× the hand-written GROUP BY, 1.8× FASTER than the generic query route, and 146.6× the one-pass loop. The range row is not the planner's price: the statement selects two COLUMNS where the store renders and parses a whole JSON document per row, which is what storing documents costs.

And what a refinement costs, with the loss in it: A window measured in time is not pushed: the store answers it at 17.7× the kernel over an array already in memory, over 100,000 candidates the index bounded. The batched as-of join reads 91,682 rows in 2 statements and costs 1477.0× fifty-one separate index reads — a bound is what it buys, not a speed-up. Without a tolerance the open side has no bound the probes imply, so the plan reads the data's own: the last instant each series carries at or before the earliest probe, folded to the least of them. That anchor is the extra statement, and what it saves depends on where the probes sit — evenly spread ones, as here, leave the least below them to skip.

A refinement is named, never quiet. explain().series reports mode — native, hybrid or engine — the declared index the fetch seeks through, the instant bounds it used, which kernel finished the answer, and a reason code for every thing the database could not do: fill-policy, calendar-width, named-zone, rolling-refinement, asof-refinement, unsupported-aggregate, nonliteral-spec, instant-not-integer, missing-series-prefix, row-selector, value-not-numeric, nonnative-grouping, invalid-spec. strict: true refuses every one of them before a statement runs, and the counts explain() prints are the LAST ACTUAL execution's — null until the document has run, because an estimate wearing a count's name is worse than no number. Every path counts: a set residual, a row projection, a native aggregate (whose candidates stays null, since no row reached the engine and SQLite reports no visited-row count) and a cursor, which counts as it is drained and is final when it settles — read mid-iteration, counts.partial is true. series.mode follows the plan mode: a selection the engine finishes is hybrid when the index narrowed the fetch and engine when nothing did, never native on the strength of an index alone.

The as-of join is bounded, and the bound is the claim. $asof with the collection on the right reads the probes it was given, bounds the fetch by their own span and by a membership test over their by keys, and issues a FIXED number of statements whatever the probes number — the failure mode a batch exists to refuse is one seek per left row, and test/db/statement-count.test.js pins the count at 1, 10 and 200 probes. Without a tolerance the open side has no bound the probes imply — the row that answers the earliest probe may lie arbitrarily far before it — so the plan asks the database for the data's own: per series, the last instant at or before that probe, folded to the least of them. That anchor is one aggregate read through the same declared index, bound through a typed slot, and it is the second statement.

What it saves depends on where the probes sit, so the benchmark publishes the candidate count beside the timing rather than netting it out, over probes spread evenly across the whole span — which is the anchor at its worst, since the earliest of them has almost nothing below it to skip. At fifty-one such probes over a hundred thousand rows the batch still LOSES to fifty-one separate index reads. Few questions of a large series belong to a batch; a join of two series does.

$overlaps narrows through a declared interval. Type a member as an object whose start and end are both required and both numeric, map each bound to a column, and the half-open test pushes: two comparisons over the declared columns, with the engine's own operator deciding over what comes back. It stays a pre-filter rather than an exact translation because §8.16 RAISES on a span whose end is at or before its start, and no schema keyword forbids storing one — so the statement keeps every inverted row and the operator raises over it exactly as it would have. That disjunct compares two columns, which no index bounds, so the fetch scans; what it buys is that the pruned rows never reach the engine at all. An unmapped pair, a schema that admits a textual bound, or a probe that is not itself a half-open span pushes nothing and names why — strict: true answers JD0010.

One corpus, five executors, proven to agree. The committed temporal corpus (test/json/fixtures/series-corpus.json) runs through the plain references, the query vocabulary, node:sqlite, a real wasm build and both drivers again with pushdown forced off — indexed and unindexed — and every case must answer identically, plan mode and reason codes included.

Two backends, frankly

SQLite is the primary backend — 3.45 or newer, on node:sqlite, bun:sqlite, or your injected wasm build — and PostgreSQL 16+ is the second, through @jarenjs/db/postgres:

import { openStore } from '@jarenjs/db';
import { postgresDriver } from '@jarenjs/db/postgres';
import { Pool } from 'pg';                    // yours, not ours

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const store = await openStore(model, {
  driver: postgresDriver(pool, { schema: 'app' }),
});

The client is INJECTED. @jarenjs/db depends on nothing outside @jarenjs/* and imports no PostgreSQL package; anything with connect() answering { query(text, values), release?() } will do, and a pg.Pool is one as it stands. One client is acquired at open, held for the store's life (a connection owns one savepoint stack) and released exactly once at close(). The schema must exist and grant USAGE. The original session search path is restored before reuse; failed cleanup discards the client. See PostgreSQL session ownership.

The same model, the same query documents and the same differential oracle run on both. What differs is declared rather than discovered:

| | SQLite | PostgreSQL | |---|---|---| | documents | JSONB in a BLOB of a STRICT table | jsonb | | an indexed path | a VIRTUAL generated column | a STORED one, guarded by jsonb_typeof | | text ordering | the default BINARY collation | COLLATE "C", which is the same bytes | | a collection's order | the implicit rowid | a declared rid bigserial | | integer and number | INTEGER and REAL | both numeric | | a boolean member | INTEGER 1/0 | smallint 1/0 | | an untyped indexed path | ANY — compares with anything | jsonb — the predicate reads the document instead | | configuration | the closed pragma set, read back at open | the operator's; store.capabilities.pragmas is all null | | drift detection | the stored CREATE text, exactly | the structural check: columns, indexes, foreign keys | | managed change ledger | journal or native sessions, according to host | journal with durable bounded pages and optional notification hints | | leased jobs / transactional outbox | shared queue and checkpoints | shared queue and checkpoints; concurrent row-locked claims | | live queries | synchronous incremental/rerun; optional async resnapshot | optional bounded resnapshot over durable journal | | maintenance (checkpoint, integrity, optimize) | yes | no — every one of them IS a pragma | | a spatial physical: 'rtree' | an R*Tree virtual table | the B-tree over the four edge columns, and explain().prefilters[].via says so | | a transaction a statement failed in | continues | is aborted until it ends (JD2088); catch-and-continue needs a nested transaction() | | ALTER TABLE | additive only | full, so a rebuild is never needed |

PostgreSQL has bounded native cursors, finite admission and a read-back server statement timeout; SQLite's statementTimeout remains false. Neither backend advertises row estimates. See host limits and cleanup for native versus buffered modes and cancellation settlement. PostgreSQL journal capture records participating managed writes; its ordering and coverage remain explicit. Portable replication uses SQLite session/journal capture and PostgreSQL managed journal capture with native bounded cursors. Cross-process concurrency on SQLite is its own story — WAL plus a busy timeout, both set and visible on store.capabilities; on PostgreSQL it is the server's, and a serialization failure or a deadlock arrives as class: 'busy', retryable: true — the same verdict, and the same caller branch, a locked SQLite file gets.

What PostgreSQL costs

npm run benchmark:postgres -- --docs 500 --write executes the same model and queries against SQLite and an injected PostgreSQL endpoint, checks identical answers, and records the operation costs. The ordinary recipe can show SQLite alone; writing accepted evidence requires both engines. The committed result and source hashes feed this table.

Measured 2026-09-19T11:52:35.163Z: 500 documents, Node 24.20.0, SQLite 3.53.4, PostgreSQL 17.11 (Debian 17.11-1.pgdg12+2), pg 8.23.0. Durability settings: fsync=on, synchronous_commit=on, full_page_writes=on.

| Operation | SQLite ms | PostgreSQL ms | PG / SQLite | Client query calls per PG operation | Iterations | |---|---:|---:|---:|---:|---:| | open | 16.872 | 18.587 | 1.1× | 17 | 1 | | insert | 0.015 | 0.561 | 38.5× | 1 | 500 | | get | 0.012 | 0.592 | 48.3× | 5 | 50 | | indexed | 0.524 | 2.950 | 5.6× | 13 | 20 | | scanned | 0.552 | 2.675 | 4.8× | 13 | 20 | | range | 0.599 | 2.531 | 4.2× | 13 | 20 | | transaction | 0.097 | 0.915 | 9.5× | 3 | 20 | | migration (one index) | 10.410 | 56.260 | 5.4× | not separately counted | 1 |

Sequential insert throughput: SQLite 68684, PostgreSQL 1782 documents/second.

| First-row probe | SQLite | PostgreSQL | |---|---:|---:| | First row ms | 0.860 | 0.893 | | First row plus cleanup ms | 0.934 | 1.212 | | Returned rows | 1 | 1 | | Fetched native rows / normalized bytes | not instrumented | 64 / 5661 | | Session peak native frame rows / bytes | not instrumented | 64 / 5813 | | Client query calls including cleanup | no network | 5 | | Sampled RSS before / after MiB | 85.64 / 103.18 | 107.93 / 114.52 | | Sampled heap before / after MiB | 20.22 / 35.29 | 14.61 / 21.38 |

After close: driver active=0, queued=0; native cursors=0, prepared statements=0; host pool total=1, idle=1, waiting=0.

Same-process sequential samples; client.query calls are SQL submissions, not TCP packet counts. Memory samples include shared process history and exclude server RSS. First row and cleanup are measured separately. No production latency claim.

client.query counts SQL submissions, not physical packets. Native cursor reads also pay BEGIN/DECLARE/FETCH/CLOSE/COMMIT costs to retain bounded rows and explicit cleanup; the old buffered driver did not provide those bounds. First-row probes return one item and close immediately. The reported peak frame spans the measured session, including earlier full queries. Migration uses a second independent shadow session. Memory samples are sequential observations of the same process, not isolated backend peaks; PostgreSQL server memory is excluded.

Historical sample, retained from the earlier backend: 500 documents — PostgreSQL 17.5 in a container on the same host, SQLite in memory, Node 24.19.0 — every row a loss except the first, and every one expected:

| operation | ratio, PostgreSQL over SQLite | |---|---| | open the store (create or verify the shape) | 0.9× | | insert one document | 14.5× | | get one document by key | 12.8× | | an indexed equality over 500 documents | 1.6× | | an indexed range over 500 documents | 1.6× | | an unindexed equality over 500 documents | 1.5× | | one transaction with one write | 4.7× | | apply one migration (one new index) | 4.3× |

This older sample used a different runtime, server profile and cursor strategy. It is not an isolated before/after regression experiment. Higher ratios mean more latency; do not infer a universal win from one operation or hide a cost by switching native reads to unbounded buffering. Batch application work inside bounded transactions where its atomicity permits. Your workload, network and server configuration determine deployment costs.

The relational half (phase B)

• Entities and relations (x-entity, MODEL-FORMAT §9): hybrid rows — key and mapped scalar columns beside one JSONB document — with real foreign keys (PRAGMA foreign_keys set AND verified), all three relation kinds, and derived epoch columns for indexed instant ranges.
• One-statement graph loads (§10): entity('User').load({ include: { posts: { include: { comments: true } } } }) runs in exactly ONE statement regardless of depth or parent count — asserted by a counting driver in the tests, and published with statement counts beside the timings on the benchmark page. Keyset pagination when the ordering allows it, reported, never silent.
• The unit of work (§11): reads are plain deep-frozen JSON (no proxies, asserted); mutation is replacement; saveChanges() diffs snapshots into minimal parameterised statements in one transaction, with insert batching, JD0040 cycle refusal, JD2040 optimistic conflicts, and a report of every statement, fallback and count. Its fate is its transaction's: an enclosing rollback WITHDRAWS the advance, so a caller's retry plans the same statements again instead of reporting a success it never had.
• Transaction ownership (MODEL-FORMAT §5.1): the store a transaction callback receives IS the transaction — tx.collection, tx.entity, tx.sync, tx.jobs, tx.saveChanges(), and tx.transaction() nesting through its savepoint. A store-level handle is by construction somebody else: it holds the connection for its own extent, so an unrelated writer on a shared store keeps its own fate rather than sharing a rollback it knows nothing about. Contention waits under queueTimeout and then names itself JD0012, or refuses at once under openStore(model, { transactions: 'strict' }). One store is safe for a handler per request. Every tx view is pinned to its EXACT scope: a handle retained past its callback, or an outer handle used while an async inner savepoint is open, refuses JD2070 instead of joining a transaction it does not own — and a transaction view carries no close, because it never owns the connection's lifetime. A root cursor (collection.query(), entity.cursor(), entity.loadCursor()) is admitted per pull: construction holds nothing, each next() and return() borrows the gate for one item's work and releases before it settles, so a paused consumer blocks no transaction and no pull ever reads another transaction's uncommitted row; store.live() registers under the same gate through its initial query.
• Named savepoints (MODEL-FORMAT §5.2): tx.savepoints.create / rollbackTo / release give a live transaction checkpoint-and-continue without a sentinel exception — the target stays active after a rollback, release keeps the rows, labels never reach SQL, and the tracker, capture stream and tx.jobs outbox all agree with the database after every partial rollback. The synchronous twin is tx.sync.savepoints; a blank, duplicate or unknown label is JD2071.
• Generated types: entityEmitModel + @jarenjs/emit render the model into entity interfaces, input variants and an EntityMetaMap; typedStore (from @jarenjs/db/typed) types every read, checks every write, and widens load results by their include specification.
• Membership (§11.7): link(own, member, target) and unlink attach and detach one many-to-many membership at a time through the unit of work — written against the join table as it stands at save time, so a repeated save changes nothing.
• The front door: @jarenjs/linq/db opens this store behind a client typed from the model pen — db.entities.Post.where((p) => p.stars.ge(3)) is the chain over the entity set, pushed down; db.entities.User.include((u) => u.posts, { where: (p) => p.stars.ge(3), take: 2 }).toArray() emits exactly the load spec above and runs in the same one statement; link/unlink reach §11.7 and live the registration below. It imports this package as an optional peer; this package never imports it.
• Relational migrations and the jaren-db CLI (MIGRATION-FORMAT §§9–12): the strategy-table diff, the documented twelve-step table rebuild with foreign_key_check inside the transaction, shape EQUALITY against a fresh build as the acceptance criterion, drift detection, and jaren-db check for CI.

Execution hosts

@jarenjs/db/node-worker exports nodeWorkerDriver() for SQLite off the caller thread; @jarenjs/db/node-pool exports nodeWorkerPoolDriver() for one writer and bounded read-only WAL workers. Both use the ordinary Store contract, with credited row frames, generation fencing, queue refusals and inspected lifecycle metrics. Async connections expose no store.sync. For live queries, configure the optional asyncLive() helper and durable journal; the declared mode is resnapshot. See execution hosts for options, cleanup guarantees, native-call shutdown limits, the browser persistence matrix and measured latency/memory losses.

The reactive and durable half (phase C)

• Change capture (LIVE-FORMAT §§1–6): every committed write becomes an observable stream of RFC 6902 patches — from SQLite's own session changesets where the binding has them, from a write-path journal where it does not (bun:sqlite, Node worker connections). The wasm adapter probes the live session API and names a failed probe in sessionReason. One diff format runs store → patch → live query → O(k) render. Capture is opt-in; the overhead is published, not waved away.
• Portable replication (REPLICATION-FORMAT): opt-in replica identities, causal frontiers, bounded logical envelopes, durable replay receipts, explicit conflict evidence and snapshot resets. Data and acknowledgements commit together. Hosts supply transport and any conflict resolver; the default preserves both contenders and rejects the write.
• Live queries (LIVE-FORMAT §§7–13): collection.live(document) maintains a result as writes arrive and emits patches — incremental for where/select/orderBy+limit/aggregates/single-level groupBy and a spatial where over a derived index (the geofence; the normative maintenance table), indexed inner/left entity joins, bounded graph projections and explicit two-level groups; re-run for other shapes, declared, never silent (live.mode names the reason). Unaffected rows stay reference-identical; a seeded oracle holds the maintained result equal to a fresh re-query after every mutation.
• Event time (LIVE-FORMAT §13): a $resample or $rolling view over a fixed width maintains exact event-time buckets and windows against a watermark the HOST supplies — never a clock — and a reading behind the declared lateness re-reads and emits a lateData record rather than being folded in as though it had arrived on time.
• Durable runs and the job queue (JOBS-FORMAT, FLOW-FORMAT §7.6): a @jarenjs/flow DAG run checkpoints declared nodes and RESUMES after a crash; store.jobs leases work in one guarded statement (no distributed lock), retries with backoff, dead-letters, and reclaims expired leases as recovery. Every settling call carries a FENCE — the opaque token one claim mints, plus a lease that is still valid — so an expired or superseded attempt cannot mark a job done over the live one's result, and is told which of three things happened rather than answered false. jobs.renew() replaces a lease while a handler runs; the worker does it automatically, retries a renewal that failed for a mere storage reason, and aborts only an attempt whose lease is proven lost by a fence code. Settlement is exactly-once against the store — an external effect is still the handler's to make idempotent. Ownership follows the handle: root store.jobs.* and all worker I/O take the store gate and never join an open application transaction, while tx.jobs is the transactional outbox that co-commits with it; and worker.stop() quiesces every claim, renewal, checkpoint and settlement before it resolves, so closing the store releases the database file deterministically. DAG jobs inspect persisted workflow/input/task identity before reading checkpoint values. jobs.reset(id, { expectedGeneration }) explicitly discards an inactive run's checkpoints and restarts it under a new fence; live leases and stale observations refuse (JOBS-FORMAT §10).
• The browser (@jarenjs/db/wasm): the same store, the same queries, the same live updates run on the official SQLite wasm build over a probed persistence ladder: isolated SharedArrayBuffer OPFS, header-free SAH-pool OPFS, atomic IndexedDB snapshots, then visibly non-durable memory. One tab owns the connection, others are clients. Proven in the #/data studio across Chromium, Firefox and WebKit, whose boot is a closed five-stage protocol (LIVE-FORMAT §11): every attempt ends ready or in a named, retryable DATA_BOOT failure, never a status line stuck at boot. The subpath exports the two helpers that studio is built on: sqlite3Handle(sqlite3, { DbClass }) builds the injected handle from a loaded wasm module and the database class the host picks (sqlite3.oo1.DB in memory, the SAH-pool OpfsSAHPoolDb or OpfsDb